package com.geologyoa.persist;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.geologyoa.commons.JdbcUtil;
import com.geologyoa.domain.Roles;
import com.geologyoa.domain.UserInfo;

public class UserInfoDaoImpl implements UserInfoDao {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	public Boolean CheckUserInfo(String username) {
		Boolean flag =false;
		   conn=JdbcUtil.getConnection();
		   String sql = "";
		   sql = "select * from userInfo where username=?";
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, username);
				int number=ps.executeUpdate();
				if(number>0)
				{
					flag=true;
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return flag;
	}

	public Boolean AddUserInfo(UserInfo userInfo) {
		Boolean flag =false;
		   conn=JdbcUtil.getConnection();
		   String sql = "";
		  sql = "insert into userInfo values(oa_seq.nextval,?,?,?,?,?,?)";
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, userInfo.getUsername());
				ps.setString(2, userInfo.getPassword());
				ps.setString(3, userInfo.getNickname());
				ps.setString(4, userInfo.getUnit());
				ps.setString(5, userInfo.getPosition());
				ps.setInt(6, userInfo.getRoleid());
//				ps.setString(2,roles.getFunction());
//				ps.setString(3, roles.getRolefunction());
				int number=ps.executeUpdate();
				if(number>0)
				{
					flag=true;
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return flag;
	}

	public List<UserInfo> SearchUserInfo(String username, String password) {
		UserInfo userInfo=null;
		List<UserInfo> userList=new ArrayList<UserInfo>();
		   conn=JdbcUtil.getConnection();
		   String sql = "";
		   sql = "select * from UserInfo where username=? and password=?";
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, username);
				ps.setString(2, password);
				rs=ps.executeQuery();
				while(rs.next())
				{
					userInfo=new UserInfo();
					userInfo.setUserid(Integer.parseInt(rs.getString("userid")));
					userInfo.setUsername(rs.getString("username"));
					userInfo.setPassword(rs.getString("password"));
					userInfo.setNickname(rs.getString("nickname"));
                    userInfo.setUnit(rs.getString("unit"));
                    userInfo.setPosition(rs.getString("position"));
                    userInfo.setRoleid(rs.getInt("roleid"));
					userList.add(userInfo);
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return userList;
	}

	public Boolean UpdateUser(UserInfo userInfo) {
		 Boolean flag =false;
		   conn=JdbcUtil.getConnection();
		   String sql = "";
		   sql = "update UserInfo set username=?,password =?,nickname=?,unit=?,position=?,roleid=? where userid=?";
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, userInfo.getUsername());
				ps.setString(2, userInfo.getPassword());
				ps.setString(3, userInfo.getNickname());
				ps.setString(4, userInfo.getUnit());
				ps.setString(5, userInfo.getPosition());
				ps.setInt(6, userInfo.getRoleid());
				ps.setInt(7, userInfo.getUserid());
				int number=ps.executeUpdate();
				if(number>0)
				{
					flag=true;
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return flag;
	}

	public List<UserInfo> SearchUser(String username, String nickname) {
		UserInfo userInfo=null;
		List<UserInfo> userList=new ArrayList<UserInfo>();
		   conn=JdbcUtil.getConnection();
		   StringBuffer sql = new StringBuffer();	
		   sql.append("select a.userid,a.username,a.password,a.nickname,a.unit,a.position,a.roleid,b.rolename as rolename from UserInfo a, roles b where 1=1 and a.roleid=b.roleid");
		      if(username.equals("")==false)
		      {
		    	  sql.append(" and username like '%"+username+"%'");  
		      }
		    	  
		      if(nickname.equals("")==false)
		      {
		    	  sql.append(" and nickname like '%"+nickname+"%'");  
		      }
			try {
				ps = conn.prepareStatement(sql.toString());
//				ps.setString(1, username);
//				ps.setString(2, nickname);
				rs=ps.executeQuery();
				while(rs.next())
				{
					userInfo=new UserInfo();
					userInfo.setUserid(Integer.parseInt(rs.getString("userid")));
					userInfo.setUsername(rs.getString("username"));
					userInfo.setPassword(rs.getString("password"));
					userInfo.setNickname(rs.getString("nickname"));
                    userInfo.setUnit(rs.getString("unit"));
                    userInfo.setPosition(rs.getString("position"));
                    userInfo.setRoleid(rs.getInt("roleid"));
                    userInfo.setRolename(rs.getString("rolename"));
                    
					userList.add(userInfo);
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return userList;
	}

	public Boolean DelUserInfo(String userid) {
		Boolean flag =false;
		   conn=JdbcUtil.getConnection();
		   String sql = "";
		   sql = "delete from userinfo  where userid=?";
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, userid);
				int number=ps.executeUpdate();
				if(number>0)
				{
					flag=true;
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return flag;
	}

	public Boolean DelUserRoleID(String roleid) {
		Boolean flag =false;
		   conn=JdbcUtil.getConnection();
		   String sql = "";
		   sql = "delete from userinfo  where roleid=?";
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, roleid);
				int number=ps.executeUpdate();
				if(number>0)
				{
					flag=true;
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return flag;
	}

	public Boolean CheckUserInfoRoID(String roleid) {
		Boolean flag =false;
		   conn=JdbcUtil.getConnection();
		   String sql = "";
		   sql = "select * from userInfo where roleid=?";
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, roleid);
				int number=ps.executeUpdate();
				if(number>0)
				{
					flag=true;
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return flag;
	}
	public Boolean CheckUserInfoExceptId(String username,String userid) {
		Boolean flag =false;
		   conn=JdbcUtil.getConnection();
		   String sql = "";
		   sql = "select * from userInfo where username=? and USERID !="+userid;
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, username);
				int number=ps.executeUpdate();
				if(number>0)
				{
					flag=true;
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return flag;
	}
}
